﻿using DBUtil;
using NUnit.Framework;
using Shouldly;
using System;
using System.Collections.Generic;

namespace Test.MySql.Inserts.InsertDictionary
{
    [TestFixture]
    internal class Index : TestBase
    {
        [Test]
        public void TestInsertOnlyTableName()
        {
            //insert no dictionary
            db.ExecuteSql(db.Manage.DropTableIfExistSql("t_user"));
            db.ExecuteSql("create table t_user(id int auto_increment primary key,name varchar(50),age int,birth datetime,addr varchar(200))");

            //ExecuteAffrows
            var sql = "";
            var insert = db.Insert("t_user")
                .SetColumn("name", "刘备")
                .SetColumn("age", 40)
                .SetColumn("birth", DateTime.Parse("1900-01-02"))
                .SetColumn("addr", "桃园");
            sql = insert.ToSql();
            sql.ShouldBe("insert into `t_user`(`name`,`age`,`birth`,`addr`) values('刘备',40,'1900-01-02','桃园');");
            var r = insert.ExecuteAffrows();
            r.ShouldBe(1);
            var ent = db.SelectModel<(int id, string name, int age, DateTime birth, string addr)>("select id,name,age,birth,addr from t_user where id=1");
            ent.id.ShouldBe(1);
            ent.name.ShouldBe("刘备");
            ent.age.ShouldBe(40);
            ent.birth.ShouldBe(DateTime.Parse("1900-01-02"));
            ent.addr.ShouldBe("桃园");

            //ExecuteIdentity
            sql = insert.ToSql(EnumInsertToSql.ExecuteIdentity);
            sql.ShouldBe("""
                insert into `t_user`(`name`,`age`,`birth`,`addr`) values('刘备',40,'1900-01-02','桃园');
                select last_insert_id();
                """);
            var id = insert.ExecuteIdentity();
            id.ShouldBe(2);

            //IgnoreColumns
            insert.IgnoreColumns("addr");
            sql = insert.ToSql();
            sql.ShouldBe("insert into `t_user`(`name`,`age`,`birth`) values('刘备',40,'1900-01-02');");

            //OnlyColumns
            insert.OnlyColumns("name", "age");
            sql = insert.ToSql();
            sql.ShouldBe("insert into `t_user`(`name`,`age`) values('刘备',40);");

            //ExecuteInserted not supported
            try
            {
                sql = insert.ToSql(EnumInsertToSql.ExecuteInserted);
                throw new Exception("error");
            }
            catch (Exception ex)
            {
                ex.Message.ShouldBe($"{insert.Type}模式无法获取新增的数据!");
            }
        }

        [Test]
        public void TestInsertOnlyTableNameToDataTable()
        {
            //ToDataTable BulkCopy
            db.ExecuteSql(db.Manage.DropTableIfExistSql("t_user"));
            db.ExecuteSql("create table t_user(id int auto_increment primary key,name varchar(50),age int,birth datetime,addr varchar(200))");

            var insert = db.Insert("t_user")
                .SetColumn("name", "刘备")
                .SetColumn("age", 40)
                .SetColumn("birth", DateTime.Parse("1900-01-02"))
                .SetColumn("addr", "桃园");
            var dt = insert.ToDataTable();
            dt.TableName.ShouldBe("t_user");
            dt.Rows.Count.ShouldBe(1);
            dt.Columns.Count.ShouldBe(4);
            dt.Rows[0]["name"].ShouldBe("刘备");
            dt.Rows[0]["age"].ShouldBe(40);
            dt.Rows[0]["birth"].ShouldBe(DateTime.Parse("1900-01-02"));
            dt.Rows[0]["addr"].ShouldBe("桃园");
            db.BulkCopy(dt);

            var dics = db.SelectDictionaryList("select * from t_user");
            dics.Count.ShouldBe(1);
            var dic = dics[0];
            dic["id"].ShouldBe(1);
            dic["name"].ShouldBe("刘备");
            dic["age"].ShouldBe(40);
            dic["birth"].ShouldBe(DateTime.Parse("1900-01-02"));
            dic["addr"].ShouldBe("桃园");
        }

        [Test]
        public void TestInsertWithDictionary()
        {
            db.ExecuteSql(db.Manage.DropTableIfExistSql("t_user"));
            db.ExecuteSql("create table t_user(id int auto_increment primary key,name varchar(50),age int,birth datetime,addr varchar(200))");

            //ExecuteAffrows
            var sql = "";
            var insert = db.Insert("t_user", new[]{ new Dictionary<string, object>
            {
                {"name","刘备" },
                {"age",40 },
                {"birth",DateTime.Parse("1900-01-02") },
                {"addr","桃园" },
            }, new Dictionary<string, object>
            {
                {"name","关羽" },
                {"age",38 },
                {"birth",DateTime.Parse("1902-01-02") },
                {"addr","桃园" },
            } });
            sql = insert.ToSql();
            sql.ShouldBe("""
                insert into `t_user`(`name`,`age`,`birth`,`addr`) values
                    ('刘备',40,'1900-01-02','桃园'),
                    ('关羽',38,'1902-01-02','桃园');
                """);
            var r = insert.ExecuteAffrows();
            r.ShouldBe(2);

            //ExecuteIdentity
            sql = insert.ToSql(EnumInsertToSql.ExecuteIdentity);
            sql.ShouldBe("""
                insert into `t_user`(`name`,`age`,`birth`,`addr`) values
                    ('刘备',40,'1900-01-02','桃园'),
                    ('关羽',38,'1902-01-02','桃园');
                select last_insert_id()+1;
                """);
            var id = insert.ExecuteIdentity();
            id.ShouldBe(4);

            //ExecuteInserted not supported
            try
            {
                sql = insert.ToSql(EnumInsertToSql.ExecuteInserted);
                throw new Exception("error");
            }
            catch (Exception ex)
            {
                ex.Message.ShouldBe($"{insert.Type}模式无法获取新增的数据!");
            }
        }

        [Test]
        public void TestInsertWithDictionaryToDataTable()
        {
            db.ExecuteSql(db.Manage.DropTableIfExistSql("t_user"));
            db.ExecuteSql("create table t_user(id int auto_increment primary key,name varchar(50),age int,birth datetime,addr varchar(200))");

            var insert = db.Insert("t_user", [
                new Dictionary<string, object>
                {
                    {"name","刘备" },
                    {"age",40 },
                    {"birth",DateTime.Parse("1900-01-02") },
                    {"addr","桃园" },
                }, new Dictionary<string, object>
                {
                    {"name","关羽" },
                    {"age",38 },
                    {"birth",DateTime.Parse("1902-01-02") },
                    {"addr","桃园" },
                }
            ]);
            var dt = insert.ToDataTable();
            db.BulkCopy(dt);
            var dics = db.SelectDictionaryList("select * from t_user");
            dics.Count.ShouldBe(2);
            var dic = dics[0];
            dic["id"].ShouldBe(1);
            dic["name"].ShouldBe("刘备");
            dic["age"].ShouldBe(40);
            dic["birth"].ShouldBe(DateTime.Parse("1900-01-02"));
            dic["addr"].ShouldBe("桃园");
            dic = dics[1];
            dic["id"].ShouldBe(2);
            dic["name"].ShouldBe("关羽");
            dic["age"].ShouldBe(38);
            dic["birth"].ShouldBe(DateTime.Parse("1902-01-02"));
            dic["addr"].ShouldBe("桃园");
        }
    }
}
